<?php if (!defined('BASEPATH')) exit('No direct script access allowed');
class Question_model extends CI_Model
{
    private $question_table_name   = 'questions';
    private $question_comments_table_name   = 'question_comments';
    private $vote_table_name   = 'votes';
    private $user_vote_to_question_table_name   = 'user_vote_to_question';
    private $fe_user_info_table_name   = 'fe_user_info';
    private $error = array();
    
    function __construct()
    {
        parent::__construct();
        $ci =& get_instance();
    }


    function insert_new_comment($user_id,$comment,$question_id,$images){
        $comment_time = date('Y-m-d H:i:s', time());
        $data = array('user_id'=>$user_id,'question_id'=>$question_id,'comment'=>$comment,'comment_time'=>$comment_time);
        
        if(!empty($images)){
            $data['images'] = json_encode($images);
        }

        $this->db->insert($this->question_comments_table_name, $data);
        
        if($this->db->affected_rows() > 0 ){ // insert into question table success
            
            // return comment user info and comment data

            $this->db->select('*');
            $this->db->from($this->fe_user_info_table_name);
            $this->db->where('uid',$user_id);
            $query = $this->db->get();
            $temp = $query->result_array();
            $return = array();

            $return['user_id'] = $user_id;
            $return['name'] = !empty($temp[0]['name'])?$temp[0]['name']:$temp[0]['email'];
            $return['comment'] = $comment;
            $return['comment_time'] = $comment_time;
            
            return $return;

        }
        else return false;
    }

    function insert_new_question($user_id,$title,$desc,$images,$photo_desc){
        $data = array('uid'=>$user_id,'title'=>$title,'desc'=>$desc,'create_time'=> date('Y-m-d H:i:s', time()));
        $this->db->insert($this->question_table_name, $data);
        $question_id = $this->db->insert_id();
        if($this->db->affected_rows() > 0 ){ // insert into question table success
            // than insert into vote table
            $photo_desc = json_decode($photo_desc,true);
            foreach ($images as $key => $value) {
                $this->insert_new_vote($question_id,$value,$photo_desc[$key]);
            }

            return true;

        }
        else return false;
    }

    function insert_new_vote($question_id,$image_file_name,$photo_desc){
        $data = array('question_id'=>$question_id,'image'=>$image_file_name,'photo_desc'=>$photo_desc);
        $this->db->insert($this->vote_table_name, $data);
        return;
    }

    function get_questions($offset=0,$limit=10){
        $this->db->select('*,'.$this->vote_table_name.'.id as vote_id');
        $this->db->join($this->vote_table_name, $this->question_table_name.'.id = '.$this->vote_table_name.'.question_id');
        $this->db->join($this->fe_user_info_table_name, $this->question_table_name.'.uid = '.$this->fe_user_info_table_name.'.uid');
        $this->db->from($this->question_table_name);
        $this->db->limit( $limit, $offset);
        $this->db->group_by($this->question_table_name.".id"); 
        $this->db->order_by("create_time", "desc"); 
        $query = $this->db->get();
        //echo $this->db->last_query();exit;
        return $query->result_array();
    }

    function get_question_by_id($question_id){
        $this->db->select('*');
        $this->db->from($this->question_table_name);
        $this->db->join($this->fe_user_info_table_name, $this->question_table_name.'.uid = '.$this->fe_user_info_table_name.'.uid');
        $this->db->where('id',$question_id);
        $query = $this->db->get();
        return $query->result_array();
    }

    function get_question_comment_counts(){
        $this->db->select('count(id) as comment_counts,question_id');
        $this->db->from($this->question_comments_table_name);
        $this->db->group_by('question_id'); 
        $query = $this->db->get();

        $comment_counts = array();

        if($query->num_rows() > 0){
            $temp = $query->result_array();

            foreach($temp as $key=>$val){
                $comment_counts[$val['question_id']] = $val['comment_counts'];
            }
            
        }

        return $comment_counts;
    }

    function get_votes_by_question_id($question_id){
        $this->db->select('*');
        $this->db->from($this->vote_table_name);
        $this->db->where('question_id',$question_id);
        $query = $this->db->get();
        return $query->result_array();
    }

    function get_comments_by_question_id($question_id){
        $this->db->select('*');
        $this->db->from($this->question_comments_table_name);
        $this->db->join($this->fe_user_info_table_name, $this->question_comments_table_name.'.user_id = '.$this->fe_user_info_table_name.'.uid');
        $this->db->where('question_id',$question_id);
        $query = $this->db->get();
        return $query->result_array();
    }

    function vote_item($question_id,$vote_id,$user_id){

        if($this->check_if_voted($question_id,$user_id)){
            return -1; // user had voted this item
        }
        else{
            $this->db->where('id', $vote_id);
            $this->db->set('vote_counts', 'vote_counts+1', FALSE);
            $this->db->update($this->vote_table_name);

            if($this->db->affected_rows() > 0 ){ // update record successfully
                // than insert user to vote table
                $data = array('user_id'=>$user_id,'question_id'=>$question_id);
                $this->db->insert($this->user_vote_to_question_table_name, $data);

                // return current vote counts
                $this->db->select('vote_counts');
                $this->db->from($this->vote_table_name);
                $this->db->where('id',$vote_id);
                $query = $this->db->get();
                
                $temp = $query->result_array();
                $current_vote_counts = $temp[0]['vote_counts'];
                return $current_vote_counts;

            }
            else return false;
        }
        
    }

    function check_if_voted($question_id,$user_id){
        $this->db->select('*');
        $this->db->from($this->user_vote_to_question_table_name);
        $this->db->where('question_id',$question_id);
        $this->db->where('user_id',$user_id);
        $query = $this->db->get();
        if($query->num_rows() > 0){
            return true;
        }
        else return false;
    }

    /****************************************/
    
    function check_join($activity_id){
        /*
         * check joined first
         * if joined count == 0 then return true
         */
        $array = array('activity_id' => $activity_id, 'kind' => 'wanna_join');
        $this->db->select('*');
        $this->db->from($this->activity_pool_table_name);
        $this->db->where($array);
        $now_joined_count = $this->db->count_all_results();
        if($now_joined_count == 0){
            return true;//still can join this activity
        }
        
        
        /*
         * if joined count > 0 then process second check
         */
        $array = array($this->activity_pool_table_name.'.activity_id' => $activity_id,$this->activity_pool_table_name.'.kind' => 'wanna_join');
        $this->db->select('*');
        $this->db->join($this->activity_info_table_name, $this->activity_pool_table_name.'.activity_id = '.$this->activity_info_table_name.'.activity_id');
        $this->db->from($this->activity_pool_table_name);
        $this->db->where($array);
        $query = $this->db->get();
        $temp = $query->result_array();
        $join_activity = $temp[0];
        $max_join_p = $join_activity['activity_max_p'];
        $now_wanna_join_count = $this->db->count_all_results();
        $this->db->close();
        if($max_join_p-$now_wanna_join_count>0){
            return true;//still can join this activity
        }
        else return false;
        
    }
    
    function record_wanna_join($activity_id,$user_id){
        $array = array('activity_id' => $activity_id, 'user_id' => $user_id, 'kind' => 'wanna_join');
        
        $this->db->select('*');
        $this->db->from($this->activity_pool_table_name);
        $this->db->where($array);
        $msg = array();
        
        if($this->db->count_all_results() > 0){
            $this->db->close();
            $msg = array('status'=>false,'msg'=>'您已表示過參加意願！');
            return $msg;
        }
        elseif(!$this->check_join($activity_id)){
            $msg = array('status'=>false,'msg'=>'已達參加人數上限！');
            return $msg;
        }
        else{
            $this->db->insert($this->activity_pool_table_name, $array);
            if($this->db->affected_rows() > 0 ){
                
                $array = array($this->activity_pool_table_name.'.activity_id' => $activity_id,$this->activity_pool_table_name.'.kind' => 'wanna_join');
                $this->db->select('*');
                $this->db->join($this->activity_info_table_name, $this->activity_pool_table_name.'.activity_id = '.$this->activity_info_table_name.'.activity_id');
                $this->db->from($this->activity_pool_table_name);
                $this->db->where($array);
                $query = $this->db->get();
                $now_wanna_join_count = $query->num_rows();
                //echo 'now joined:'.$now_wanna_join_count;
                $temp = $query->result();
                //echo "<pre>";print_r($temp);echo "</pre>";
                $join_activity = $temp[0];
                $max_join_p = $join_activity->activity_max_p;
                
                
                $left_join_count = $max_join_p - $now_wanna_join_count;
                //echo "<pre>";print_r($query->result_array());echo "</pre>";
                
                $msg = array('status'=>true,'wanna_join_count'=>($now_wanna_join_count),'left_join_count'=>$left_join_count);
                return $msg;
            }
            else{
                $msg = array('status'=>false,'msg'=>'發生錯誤！請稍侯再試');
                return $msg;
            }
        }
    }
    
    function delete_by_kind($activity_id,$kind){
        $this->db->delete($this->activity_pool_table_name, array('activity_id' => $activity_id,'kind'=>$kind));
        if($this->db->affected_rows() > 0 ) return true;
        else return false; 
    }
    
    function delete_my_favorite($activity_id,$user_id){
        $this->db->delete($this->activity_info_table_name, array('activity_id' => $activity_id,'user_id'=>$user_id));
        if($this->db->affected_rows() > 0 ) return true;
        else return false; 
    }

    function delete($post_id){
        $this->db->delete($this->post_table_name, array('id' => $post_id));
        if($this->db->affected_rows() > 0 ) return true;
        else return false;    
    }

    function save($data,$user_id,$status=1){
        
        $remove_keys = array('file'=>'','save'=>'');
        $data['user_id'] = $user_id;
        $data['upload_image'] = isset($data['upload_image'])?json_encode($data['upload_image']):'';
        $data = array_diff_key($data, $remove_keys);
        $data['modified_time'] = date('Y-m-d H:i:s', time());
        $data['status'] = $status;
        $this->db->insert($this->post_table_name, $data);
        
        if($this->db->affected_rows() > 0 ) return true;
        else return false;
    }
    
    function save_fe_post($data,$user_id,$status=1){ // status=1 即不用審核

        $data['user_id'] = $user_id;
        $data['upload_image'] = isset($data['upload_image'])?json_encode($data['upload_image']):'';
        $data['modified_time'] = date('Y-m-d H:i:s', time());
        $data['status'] = $status;
        $this->db->insert($this->fe_post_table_name, $data);
        
        if($this->db->affected_rows() > 0 ) return true;
        else return false;

    }

    function save_outside_activity($data){
        $remove_keys = array('file'=>'','save'=>'');
        $data = array_diff_key($data, $remove_keys);
        $data['kind'] = '0';
        $this->db->insert($this->activity_info_table_name, $data);
        
        if($this->db->affected_rows() > 0 ) return true;
        else return false;
    }
    
    function modify($data,$post_id,$status=1){
        $remove_keys = array('file'=>'','save'=>'','post_id'=>'');
        $data['upload_image'] = isset($data['upload_image'])?json_encode($data['upload_image']):'';
        $data['status'] = '1';//set draft to published
        $data = array_diff_key($data, $remove_keys);
        
        $data['modified_time'] = date('Y-m-d H:i:s', time());
        $data['status'] = $status;
        $this->db->where('id', $post_id);
        $this->db->update($this->post_table_name, $data);
        
        if($this->db->affected_rows() > 0 ) return true;
        else return false;
    }

    function update_home_view_status($id,$status){
        $this->db->where('id', $id);
        $this->db->update($this->fe_post_table_name, array('home_view_status'=>$status));
        
        if($this->db->affected_rows() > 0 ) return true;
        else return false;

    }
    
    function get($user_id){
        $activities = array();
        // get organizer establish activities first
        $this->db->order_by("activity_id", "desc");
        $query = $this->db->get_where($this->activity_info_table_name, array('user_id' => $user_id,'kind' => '1','status' => '1'));//kind:1 for inside activity
        
        $activities['organizer'] = $query->result_array();
        
        $this->db->select('*');
        $this->db->from($this->activity_pool_table_name);
        $this->db->join($this->activity_info_table_name, $this->activity_pool_table_name.'.activity_id = '.$this->activity_info_table_name.'.activity_id');
        $this->db->where($this->activity_pool_table_name.'.user_id',$user_id);
        $this->db->order_by($this->activity_info_table_name.".activity_id", "desc"); 
        $query = $this->db->get();
        
        foreach($query->result_array() as $key=>$val){
            $activities[$val['kind']][] = $val;
        }
        
        return $activities;
    }
    
    function get_post_by_id($post_id){
        $post = array();
        
        $query = $this->db->get_where($this->post_table_name, array('id' => $post_id));
        
        $post = $query->result_array();
        
        
        return $post;
    }

    function get_share_by_id($post_id){
        $post = array();
        
        $this->db->join($this->fe_users_table_name, $this->fe_users_table_name.'.uid = '.$this->fe_post_table_name.'.user_id');
        $query = $this->db->get_where($this->fe_post_table_name, array($this->fe_post_table_name.'.id' => $post_id));
        
        $post = $query->result_array();
        
        
        return $post;
    }

    function get_fe_post_by_id($post_id){
        $this->db->select('*');
        $this->db->select('fe_post.id as post_id,fe_user_info.uid as user_id');
        $this->db->join($this->fe_users_table_name, $this->fe_users_table_name.'.uid = '.$this->fe_post_table_name.'.user_id');
        $this->db->from($this->fe_post_table_name);
        $this->db->where('id',$post_id);
        
        $query = $this->db->get();
        return $query->result_array();
    }


    function get_previous_next_by_current_id($post_id){
        $post = array();

        // get modified time of current id 
        $this->db->select('*')->from($this->post_table_name);
        $this->db->where('id',$post_id);
        $query = $this->db->get();
        $post['current'] = $query->result_array();
        
        //previous record
        $this->db->select('*')->from($this->post_table_name);
        $this->db->where('`modified_time` = (SELECT MIN(modified_time) FROM `'.$this->post_table_name.'` where `modified_time` > \''.$post['current'][0]['modified_time'].'\' AND `status`=1)', NULL, FALSE);
        $query = $this->db->get();
        $post['previous'] = $query->result_array();
        
        //next

        $this->db->select('*')->from($this->post_table_name);
        $this->db->where('`modified_time` = (SELECT MAX(modified_time) FROM `'.$this->post_table_name.'` where `modified_time` < \''.$post['current'][0]['modified_time'].'\' AND `status`=1)', NULL, FALSE);
        $query = $this->db->get();
        $post['next'] = $query->result_array();
        
        return $post;

    }

    function get_previous_next_share_by_current_id($post_id){
        $post = array();

        // get modified time of current id 
        $this->db->select('*')->from($this->fe_post_table_name);
        $this->db->where('id',$post_id);
        $query = $this->db->get();
        $post['current'] = $query->result_array();
        
        //previous record
        $this->db->select('*')->from($this->fe_post_table_name);
        $this->db->where('`modified_time` = (SELECT MIN(modified_time) FROM `'.$this->fe_post_table_name.'` where `modified_time` > \''.$post['current'][0]['modified_time'].'\' AND `status`=1)', NULL, FALSE);
        $query = $this->db->get();
        $post['previous'] = $query->result_array();
        
        //next

        $this->db->select('*')->from($this->fe_post_table_name);
        $this->db->where('`modified_time` = (SELECT MAX(modified_time) FROM `'.$this->fe_post_table_name.'` where `modified_time` < \''.$post['current'][0]['modified_time'].'\' AND `status`=1)', NULL, FALSE);
        $query = $this->db->get();
        $post['next'] = $query->result_array();
        
        return $post;

    }

    function get_previous_next_by_current_id_and_keyword($post_id,$keyword){
        $post = array();

        // get modified time of current id 
        $this->db->select('*')->from($this->post_table_name);
        $this->db->where('id',$post_id);
        $query = $this->db->get();
        $post['current'] = $query->result_array();
        
        $keyword_conditiion = " AND (post_category like '%".$keyword."%' OR post_desc like '%".$keyword."%' OR post_excerpt like '%".$keyword."%' OR post_tags like '%".$keyword."%')";
        
        //previous record
        $this->db->select('*')->from($this->post_table_name);
        $this->db->where('`status`=1 '.$keyword_conditiion.' AND (`modified_time` > \''.$post['current'][0]['modified_time'].'\') order by `modified_time` asc limit 1', NULL, FALSE);
        $query = $this->db->get();
        $post['previous'] = $query->result_array();

        //next
        $this->db->select('*')->from($this->post_table_name);
        $this->db->where('`status`=1 '.$keyword_conditiion.' AND (`modified_time` <  \''.$post['current'][0]['modified_time'].'\') order by `modified_time` desc limit 1', NULL, FALSE);
        $query = $this->db->get();
        $post['next'] = $query->result_array();
        return $post;

    }

    function get_previous_next_share_by_current_id_and_keyword($post_id,$keyword){
        $post = array();

        // get modified time of current id 
        $this->db->select('*')->from($this->post_table_name);
        $this->db->where('id',$post_id);
        $query = $this->db->get();
        $post['current'] = $query->result_array();
        
        $keyword_conditiion = " AND (post_category like '%".$keyword."%' OR post_desc like '%".$keyword."%' OR post_excerpt like '%".$keyword."%' OR post_tags like '%".$keyword."%')";
        
        //previous record
        $this->db->select('*')->from($this->fe_post_table_name);
        $this->db->where('`status`=1 '.$keyword_conditiion.' AND (`modified_time` > \''.$post['current'][0]['modified_time'].'\') order by `modified_time` asc limit 1', NULL, FALSE);
        $query = $this->db->get();
        $post['previous'] = $query->result_array();

        //next
        $this->db->select('*')->from($this->fe_post_table_name);
        $this->db->where('`status`=1 '.$keyword_conditiion.' AND (`modified_time` <  \''.$post['current'][0]['modified_time'].'\') order by `modified_time` desc limit 1', NULL, FALSE);
        $query = $this->db->get();
        $post['next'] = $query->result_array();
        return $post;

    }

    function get_previous_next_by_current_id_and_category_string($post_id,$category_string,$offset=0,$number=0){
        $post = array();

        // get modified time of current id 
        $this->db->select('*')->from($this->post_table_name);
        $this->db->where('id',$post_id);
        $query = $this->db->get();
        $post['current'] = $query->result_array();

        //previous record
        $this->db->select('*')->from($this->post_table_name);
        $category_conditiion = " AND `post_category` like '%".$category_string."%'";
        $this->db->where('`status`=1 '.$category_conditiion.' AND (`modified_time` > \''.$post['current'][0]['modified_time'].'\') order by `modified_time` asc limit 1', NULL, FALSE);
        $query = $this->db->get();
        $post['previous'] = $query->result_array();


        //next record
        $this->db->select('*')->from($this->post_table_name);
        $category_conditiion = " AND `post_category` like '%".$category_string."%'";
        $this->db->where('`status`=1 '.$category_conditiion.' AND (`modified_time` < \''.$post['current'][0]['modified_time'].'\') order by `modified_time` desc limit 1', NULL, FALSE);
        $query = $this->db->get();
        $post['next'] = $query->result_array();

        return $post;
    }

    function get_previous_next_share_by_current_id_and_category_string($post_id,$category_string,$offset=0,$number=0){
        $post = array();

        // get modified time of current id 
        $this->db->select('*')->from($this->fe_post_table_name);
        $this->db->where('id',$post_id);
        $query = $this->db->get();
        $post['current'] = $query->result_array();
        //previous record
        $this->db->select('*')->from($this->fe_post_table_name);
        $category_conditiion = " AND `post_category` like '%".$category_string."%'";
        $this->db->where('`status`=1 '.$category_conditiion.' AND (`modified_time` > \''.$post['current'][0]['modified_time'].'\') order by `modified_time` asc limit 1', NULL, FALSE);
        $query = $this->db->get();
        $post['previous'] = $query->result_array();


        //next record
        $this->db->select('*')->from($this->fe_post_table_name);
        $category_conditiion = " AND `post_category` like '%".$category_string."%'";
        $this->db->where('`status`=1 '.$category_conditiion.' AND (`modified_time` < \''.$post['current'][0]['modified_time'].'\') order by `modified_time` desc limit 1', NULL, FALSE);
        $query = $this->db->get();
        $post['next'] = $query->result_array();

        return $post;
    }

    function get_related_post_by_current_post($post,$post_limit = 4,$select_limit=100){
        
        $post_category = $post[0]['post_category'];
        $category_array = explode(',', $post_category);
        
        $this->db->select('*');
        $this->db->from($this->post_table_name);
        $this->db->limit($select_limit);
        $this->db->where('status', '1');
        $this->db->order_by('modified_time', 'DESC');
        foreach ($category_array as $key => $value) {
            $this->db->or_like('post_category', $value); 
        }
        $query = $this->db->get();
        
        $temp = $query->result_array();

        if($post_limit > count($temp)){
            $post_limit = count($temp);
        }

        $related_post_temp = array_rand($temp, $post_limit);
        
        foreach($related_post_temp as $key=>$value){
            $related_post[$key] = $temp[$value];
        }

        
        return $related_post;
    }

    function get_related_post_by_current_share($post,$post_limit = 4,$select_limit=100){
        $post_category = $post[0]['post_category'];
        $category_array = explode(',', $post_category);
        
        $this->db->select('*');
        $this->db->from($this->fe_post_table_name);
        $this->db->limit($select_limit);
        $this->db->where('status', '1');
        $this->db->order_by('modified_time', 'DESC');
        foreach ($category_array as $key => $value) {
            $this->db->or_like('post_category', $value); 
        }
        $query = $this->db->get();
        
        $temp = $query->result_array();

        if($post_limit > count($temp)){
            $post_limit = count($temp);
        }

        $related_post_temp = array_rand($temp, $post_limit);
        
        $related_post = array();

        if(!empty($related_post_temp)){
            foreach($related_post_temp as $key=>$value){
                $related_post[$key] = $temp[$value];
            }
        }
        
        return $related_post;
    }

    function get_related_post_by_current_topic($topic,$post_limit = 4,$select_limit=100){
        
        $topic_tag = $topic[0]['topic_tags'];
        $tag_array = explode(',', $topic_tag);
        
        if(empty($tag_array)){
            return false;
        }

        $this->db->select('*');
        $this->db->from($this->post_table_name);
        $this->db->limit($select_limit);
        $this->db->where('status', '1');
        $this->db->order_by('modified_time', 'DESC');
        foreach ($tag_array as $key => $value) {
            $this->db->or_like('post_category', $value); 
        }

        foreach ($tag_array as $key => $value) {
            $this->db->or_like('post_tags', $value); 
        }
        
        $query = $this->db->get();
        
        $temp = $query->result_array();
        
        if(empty($temp)){
            return false;
        }

        if($post_limit > count($temp)){
            $post_limit = count($temp);
        }

        $related_post_temp = array_rand($temp, $post_limit);
        
        if(!empty($related_post_temp)){
            foreach($related_post_temp as $key=>$value){
                $related_post[$key] = $temp[$value];
            }

            
            return $related_post;
        }
        else return array();
        
    }

    function get_post_by_id_string($post_id_string){
        $post = array();
        $this->db->select('*');
        $this->db->from($this->post_table_name);
        $this->db->where('status', '1');
        $this->db->where_in('id',$post_id_string);
        $query = $this->db->get();
        
        $post = $query->result_array();
        return $post;
    }

    function get_share_by_id_string($post_id_string){
        $post = array();
        $this->db->select('*');
        $this->db->from($this->fe_post_table_name);
        $this->db->where('status', '1');
        $this->db->where_in('id',$post_id_string);
        $query = $this->db->get();
        
        $post = $query->result_array();
        return $post;
    }
    
    function get_post_category(){
        $this->db->select('*');
        $this->db->from($this->post_category_table_name);
        $this->db->order_by('category_key', "asc"); 
        $query = $this->db->get();
        return $query->result_array();
    }
    
    function get_all_post(){
        $this->db->select('*');
        $this->db->select('post.id as post_id,users.id as user_id');
        //$this->db->join($this->post_category_table_name, $this->post_category_table_name.'.category_key = '.$this->post_table_name.'.post_category');
        $this->db->join($this->users_table_name, $this->users_table_name.'.id = '.$this->post_table_name.'.user_id');
        $this->db->from($this->post_table_name);
        $this->db->where('status','1');
        $this->db->order_by("modified_time", "desc"); 
        $query = $this->db->get();
        return $query->result_array();
    }

    function get_all_share_unproved(){
        $this->db->select('*');
        $this->db->select('fe_post.id as post_id,fe_user_info.uid as user_id');
        $this->db->join($this->fe_users_table_name, $this->fe_users_table_name.'.uid = '.$this->fe_post_table_name.'.user_id');
        $this->db->from($this->fe_post_table_name);
        //$this->db->where('status','1');
        $this->db->order_by("modified_time", "desc"); 
        $query = $this->db->get();
        return $query->result_array();
    }

    function accept_idea($idea_id,$post_tags){
        $this->db->where('id', $idea_id);
        $this->db->update($this->fe_post_table_name, array('status'=>1,'post_tags'=>$post_tags));
        return true;
    }

    function reject_idea($idea_id,$reject_reason){
        $this->db->where('id', $idea_id);
        $this->db->update($this->fe_post_table_name, array('status'=>2,'reject_reason'=>$reject_reason,'home_view_status'=>'false'));
        return true;
    }

    function get_post_by_offset($offset,$number){
        $this->db->select('*');
        $this->db->select('post.id as post_id,users.id as user_id');
        $this->db->join($this->users_table_name, $this->users_table_name.'.id = '.$this->post_table_name.'.user_id');
        $this->db->from($this->post_table_name);
        $this->db->where('status','1');
        $this->db->limit( $number, $offset);
        $this->db->order_by("modified_time", "desc"); 
        $query = $this->db->get();
        return $query->result_array();
    }

    function get_neweast_share(){ // get 4 newest share
        $this->db->select('*');
        $this->db->select('fe_post.id as post_id,'.$this->fe_users_table_name.'.uid as user_id');
        $this->db->join($this->fe_users_table_name, $this->fe_users_table_name.'.uid = '.$this->fe_post_table_name.'.user_id');
        $this->db->from($this->fe_post_table_name);
        $this->db->where('home_view_status','true');
        $this->db->limit(4);
        $this->db->order_by("modified_time", "desc"); 
        $query = $this->db->get();
        return $query->result_array();
    }

    function get_post_by_keyword($keyword,$offset,$number){
/*
        SELECT * FROM products 
           WHERE match(desc) against('iphone 4s' IN BOOLEAN MODE)

           SELECT * FROM table_name
WHERE MATCH (col1,col2,col3)
AGAINST ('keyword' IN NATURAL LANGUAGE MODE);
*/

        $kwds = explode(" ", $keyword);
        $kwds = array_map("mysql_real_escape_string", $kwds);
        $regexp = implode(" ", $kwds) ;

        foreach ($kwds as $key => $value) {
            //$regexp_all_match .= '%'.$value.'%';
            $kwds[$key] = "LIKE '%".$value."%' ";
        }

        $like_str = implode("AND LOWER(`post_tags`) ", $kwds) ;


        $this->db->select('*,post.id as post_id');
        $this->db->from($this->post_table_name);
        //$where = "(status = '1') AND (post_category like '%".$keyword."%' OR post_desc like '%".$keyword."%' OR post_excerpt like '%".$keyword."%' OR post_tags like '%".$keyword."%')";
        $where = "(status = '1') AND (LOWER(`post_tags`) ".$like_str.") OR (LOWER(`post_category`) REGEXP '".$regexp."' OR LOWER(`post_desc`) REGEXP '".$regexp."' OR LOWER(`post_excerpt`) REGEXP '".$regexp."')";
        //$where = "match(post_category,post_title,post_desc,post_excerpt,post_tags) against('".$regexp."' IN NATURAL LANGUAGE MODE)";
        $this->db->where($where);
        $this->db->limit( $number, $offset);
        //$this->db->order_by("modified_time", "desc"); 
        $query = $this->db->get();
        //echo $this->db->last_query();exit;
        return $query->result_array();
    }

    function get_share_by_keyword($keyword,$offset,$number){

        $kwds = explode(" ", $keyword);
        $kwds = array_map("mysql_real_escape_string", $kwds);
        $regexp = implode("|", $kwds) ;

        $regexp_all_match = '';//= implode(".*", $kwds) ;

        foreach ($kwds as $key => $value) {
            $regexp_all_match .= '(?=.*'.$value.')';
        }
        
        $this->db->select('*,fe_post.id as post_id');
        $this->db->from($this->fe_post_table_name);

        //$where = "(status = '1') AND (post_category like '%".$keyword."%' OR post_desc like '%".$keyword."%' OR post_excerpt like '%".$keyword."%' OR post_tags like '%".$keyword."%')";

        $where = "(status = '1') AND (LOWER(`post_category`) REGEXP '".$regexp."' OR LOWER(`post_desc`) REGEXP '".$regexp."' OR LOWER(`post_excerpt`) REGEXP '".$regexp."')";
        $this->db->where($where);
        $this->db->limit( $number, $offset);
        //$this->db->order_by("modified_time", "desc"); 
        $query = $this->db->get();
        return $query->result_array();
    }

    function get_all_category(){
        $this->db->select('*');
        $this->db->from($this->post_category_table_name);
        $query = $this->db->get();
        return $query->result_array();
    }

    function get_all_post_by_user_id($user_id){
        $this->db->select('*');
        $this->db->select('post.id as post_id,users.id as user_id');
        //$this->db->join($this->post_category_table_name, $this->post_category_table_name.'.category_key = '.$this->post_table_name.'.post_category');
        $this->db->join($this->users_table_name, $this->users_table_name.'.id = '.$this->post_table_name.'.user_id');
        $this->db->from($this->post_table_name);
        $this->db->where($this->post_table_name.'.user_id',$user_id);
        $this->db->order_by("modified_time", "desc"); 
        $query = $this->db->get();
        return $query->result_array();
    }

    function get_all_post_by_category_string($category_string,$offset=0,$number=0){
        $this->db->select('*');
        $this->db->select('post.id as post_id,users.id as user_id');
        //$this->db->join($this->post_category_table_name, $this->post_category_table_name.'.category_key = '.$this->post_table_name.'.post_category');
        $this->db->join($this->users_table_name, $this->users_table_name.'.id = '.$this->post_table_name.'.user_id');
        $this->db->from($this->post_table_name);
        $this->db->where($this->post_table_name.'.post_category like ','%'.$category_string.'%');
        $this->db->where('status','1');
        $this->db->limit( $number, $offset);
        $this->db->order_by("modified_time", "desc"); 
        $query = $this->db->get();
        //echo $this->db->last_query();exit;
        return $query->result_array();
    }

    function get_all_share_by_category_string($category_string,$offset=0,$number=0){
        $this->db->select('*');
        $this->db->select('fe_post.id as post_id,'.$this->fe_users_table_name.'.uid as user_id');
        //$this->db->join($this->post_category_table_name, $this->post_category_table_name.'.category_key = '.$this->post_table_name.'.post_category');
        $this->db->join($this->fe_users_table_name, $this->fe_users_table_name.'.uid = '.$this->fe_post_table_name.'.user_id');
        $this->db->from($this->fe_post_table_name);
        $this->db->where($this->fe_post_table_name.'.post_category like ','%'.$category_string.'%');
        $this->db->where('status','1');
        $this->db->limit( $number, $offset);
        $this->db->order_by("modified_time", "desc"); 
        $query = $this->db->get();
        //echo $this->db->last_query();exit;
        return $query->result_array();
    }

    function get_recent_post(){
        $this->db->select('*');
        $this->db->select('post.id as post_id');
        //$this->db->join($this->post_category_table_name, $this->post_category_table_name.'.category_key = '.$this->post_table_name.'.post_category');
        $this->db->join($this->users_table_name, $this->users_table_name.'.id = '.$this->post_table_name.'.user_id');
        $this->db->from($this->post_table_name);
        $this->db->limit(20);
        $this->db->order_by("modified_time", "desc"); 
        $query = $this->db->get();
        return $query->result_array();
    }
    
    function get_all_published_activity_by_condition($data){
        $current_date = date('Y-m-d');
        $this->db->select('*');
        $conditions = array('activity_location' => $data['activity_location'], 'activity_category' => $data['activity_category']);
        $this->db->like($conditions); 
        $this->db->from($this->activity_info_table_name);
        //$this->db->where('activity_date >=', $current_date);
        //$this->db->where('activity_date <=', $data['activity_date']);
        $this->db->where('status','1');
        $query = $this->db->get();
        //echo $this->db->_error_message();exit;
        //echo "<pre>";print_r($this->db->_error_message());echo "</pre>";exit;
        if($this->db->_error_message()){
            return false;
        }
        else{
            return $query->result_array();
        }
        
    }
    
    function get_all_published_activity_by_create_date(){
        $this->db->select('*');
        $this->db->from($this->activity_info_table_name);
        $this->db->order_by("modified_time", "desc");
        $this->db->limit(5);
        $this->db->where('status','1');
        $query = $this->db->get();
        return $query->result_array();
    }
    
    function get_join_count($activity_id){
        
        $this->db->from($this->activity_pool_table_name);
        $this->db->where($this->activity_pool_table_name.'.activity_id',$activity_id);
        $this->db->where($this->activity_pool_table_name.'.kind','join');
         
        return $this->db->count_all_results();
    }
    
    function get_wanna_join_count($activity_id){
        
        $this->db->from($this->activity_pool_table_name);
        $this->db->where($this->activity_pool_table_name.'.activity_id',$activity_id);
        $this->db->where($this->activity_pool_table_name.'.kind','wanna_join');
         
        return $this->db->count_all_results();
    }
    
    function get_left_join_count($activity_id){
        $array = array($this->activity_pool_table_name.'.activity_id' => $activity_id,$this->activity_pool_table_name.'.kind' => 'wanna_join');
        $this->db->select('*');
        $this->db->join($this->activity_info_table_name, $this->activity_pool_table_name.'.activity_id = '.$this->activity_info_table_name.'.activity_id');
        $this->db->from($this->activity_pool_table_name);
        $this->db->where($array);
        $query = $this->db->get();
        $now_wanna_join_count = $query->num_rows();
        
        if($now_wanna_join_count == 0){
            return array('left_join_count'=>'FALSE','wanna_join_count'=>0);
        }
        
        $temp = $query->result();
        
        $join_activity = $temp[0];
        $max_join_p = $join_activity->activity_max_p;
        $left_join_count = $max_join_p - $now_wanna_join_count;
        
        return array('left_join_count'=>$left_join_count,'wanna_join_count'=>$now_wanna_join_count);
    }

}
